/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.google.code.netbuilder.dal.providers.jdbc;

import com.google.code.netbuilder.dal.entities.Equipment;
import com.google.code.netbuilder.dal.entities.Port;
import com.google.code.netbuilder.dal.providers.IEquipmentProvider;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Юрийэ
 */
public class EquipmentProviderJDBC implements IEquipmentProvider{

    @Override
    public void save(Equipment entity) throws RuntimeException {
        
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        
        try {
            conn = JDBCServise.getConnection();
            conn.setAutoCommit(false);
            
            String queryInsertEquipment = "Insert into Equipment(name, creation_date, modification_date, object_state,"
                    + " type, address) Values (?, ?, ?, ?, null, ?);";
            pst1 = conn.prepareStatement(queryInsertEquipment, PreparedStatement.RETURN_GENERATED_KEYS);
            pst1.setString(1, entity.getName());
            pst1.setDate(2, new Date(entity.getCreationDate().getTime()));
            if (entity.getModificationDate() != null)
                pst1.setDate(3, new Date(entity.getModificationDate().getTime()));
            else pst1.setNull(3, java.sql.Types.DATE);
            pst1.setInt(4, entity.getObjectState());
            //pst1.setString(5, entity.getType());
            pst1.setString(5, entity.getAddress());
            
            System.out.println(pst1.toString());
            
            pst1.executeUpdate();
            
            rs1 = pst1.getGeneratedKeys();
            rs1.next();
            entity.setId(rs1.getLong(1));
            
            List<Port> ports = entity.getPorts();
            
            String queryInsertPorts = "Insert into port(name, creation_date, modification_date,"
                    + " object_state, equipment_id) Values (?, ?, ?, ?,"+ entity.getId() +");";

            pst2 = conn.prepareStatement(queryInsertPorts, PreparedStatement.RETURN_GENERATED_KEYS);
            
            for(int i = 0; i< ports.size(); i++){
                Port port = ports.get(i);
                pst2.setString(1, port.getName());
                pst2.setDate(2, new Date(port.getCreationDate().getTime()));
                if (port.getModificationDate() != null)
                    pst2.setDate(3, new Date(port.getModificationDate().getTime()));
                else pst2.setNull(3, java.sql.Types.DATE);
                pst2.setInt(4, port.getObjectState());
                pst2.executeUpdate();
                rs2 = pst2.getGeneratedKeys();
                rs2.next();
                port.setId(rs2.getLong(1));
                rs2.close();
            }
            
            String queryInsertInnerConnection = "Insert into internal_connection(from_port_id, to_port_id)"
                    + " Values(?, ?)";
            pst3 = conn.prepareStatement(queryInsertInnerConnection);
            for(int i = 0; i < ports.size(); i++){
                pst3.setLong(1, ports.get(i).getId());
                pst3.setLong(2, ports.get(i).getInnerConnectedPort().getId());
                pst3.executeUpdate();
            }           
            
            conn.commit();
        } catch (Exception ex) {
            Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, ex);
            try {
                if (conn!= null)
                    conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, ex1);
            }
            throw new RuntimeException(ex);
        }
        finally
		{
			try {
                if (rs1!= null) rs1.close();
                if (rs2!= null) rs2.close();
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (pst3 != null) pst3.close();
				if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }
		}
    }

    @Override
    public void update(Equipment entity) throws RuntimeException {
        
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        
        try {
            conn = JDBCServise.getConnection();
            conn.setAutoCommit(false);
            
            String queryUpdateEquipment = "Update Equipment set name=?, creation_date=?,"
                    + " modification_date=?, object_state=?, address=? where id="+entity.getId()+";";
            pst1 = conn.prepareStatement(queryUpdateEquipment);
            pst1.setString(1, entity.getName());
            pst1.setDate(2, new Date(entity.getCreationDate().getTime()));
            if (entity.getModificationDate() != null)
                pst1.setDate(3, new Date(entity.getModificationDate().getTime()));
            else pst1.setNull(3, java.sql.Types.DATE);
            pst1.setInt(4, entity.getObjectState());
            //pst1.setString(5, entity.getType());
            pst1.setString(5, entity.getAddress());
            
            pst1.executeUpdate();
            
            List<Port> ports = entity.getPorts();
            
            String queryUpdatePorts = "Update port set name=?, creation_date=?, modification_date=?,"
                    + " object_state=? where equipment_id="+ entity.getId() +";";

            pst2 = conn.prepareStatement(queryUpdatePorts);
            
            for(int i = 0; i< ports.size(); i++){
                Port port = ports.get(i);
                pst2.setString(1, port.getName());
                pst2.setDate(2, new Date(port.getCreationDate().getTime()));
                if (port.getModificationDate() != null)
                    pst2.setDate(3, new Date(port.getModificationDate().getTime()));
                else pst2.setNull(3, java.sql.Types.DATE);
                pst2.setInt(4, port.getObjectState());
                pst2.executeUpdate();
            }
            
            String queryUpdateInnerConnection = "Update internal_connection set to_port_id=?"
                    + " Where from_port_id=?;";
            pst3 = conn.prepareStatement(queryUpdateInnerConnection);
            for(int i = 0; i < ports.size(); i++){
                pst3.setLong(2, ports.get(i).getId());
                pst3.setLong(1, ports.get(i).getInnerConnectedPort().getId());
                pst3.executeUpdate();
            }           
            
            conn.commit();
        } catch (Exception ex) {
            Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, ex);
            try {
                if (conn!= null)
                    conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, ex1);
            }
            throw new RuntimeException(ex);
        }
        finally
		{
			try {
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (pst3 != null) pst3.close();
				if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
			} catch (SQLException e) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
				throw new RuntimeException(e);
            }
		}
    }

    @Override
    public void delete(Equipment entity) throws RuntimeException {
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        PreparedStatement pst3 = null;
        PreparedStatement pst4 = null;
        
        try{
            conn = JDBCServise.getConnection();
            conn.setAutoCommit(false);
            List<Port> ports = entity.getPorts();
            
            String queryDeleteInternalConnection = "Delete From internal_connection"
                    + " where from_port_id=? OR to_port_id=? ;";
            String queryDeletePorts = "Delete From port Where id=? ;";
            pst1 = conn.prepareStatement(queryDeleteInternalConnection);
            pst2 = conn.prepareStatement(queryDeletePorts);
            Port port = null;
            for(int i = 0; i < ports.size(); i++){
                port = ports.get(i);
                pst1.setLong(1, port.getId());
                pst1.setLong(2, port.getId());
                pst2.setLong(1, port.getId());
                pst1.executeUpdate();
                pst2.executeUpdate();
            }
            
            String queryDeleteEquipmentVersion = "Delete From equipment_version "
                    + "Where equipment_id=" + entity.getId();
            pst3 = conn.prepareStatement(queryDeleteEquipmentVersion);
            pst3.executeUpdate();
            
            String queryDeleteEquipment = "Delete From equipment where id="
                    + entity.getId();
            pst4 = conn.prepareStatement(queryDeleteEquipment);
            pst4.executeUpdate();
            conn.commit();
        } catch (Exception e){
            Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, e);
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, ex1);
                throw new RuntimeException(ex1);
            }
            throw new RuntimeException(e);
        }
        finally
		{
			try {
                if (pst1 != null) pst1.close();
                if (pst2 != null) pst2.close();
                if (pst3 != null) pst3.close();
                if (pst4 != null) pst3.close();
				if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
			} catch (SQLException e) {
				throw new RuntimeException(e);
            }
		}
    }

    @Override
    public List<Equipment> getEquipmentsByAddress(String address) {
        return getEquipmentsList("Select * From equipment where name LIKE %"+address+"%"); 
    }

    @Override
    public List<Equipment> getEquipmentsByName(String name){
        return getEquipmentsList("Select * From equipment where name LIKE %"+name+"%");        
    }
    
    @Override
    public List<Equipment> findAll() throws RuntimeException {
        return getEquipmentsList("Select * from equipment;");        
    }

    @Override
    public Equipment findByID(Integer id) throws RuntimeException {
        Connection conn = null;
		PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
		ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        Equipment e = new Equipment();
		
		try {
			conn = JDBCServise.getConnection();
        
			pst = conn.prepareStatement("Select * from Equipment where id="+id+";");
			rs = pst.executeQuery();
            if (!rs.next()) throw new RuntimeException("equipment not found");
            
            
            e.setId(rs.getLong("id"));
            e.setName(rs.getString("name"));
            e.setAddress(rs.getString("address"));
            e.setCreationDate(rs.getDate("creation_date"));
            e.setModificationDate(rs.getDate("modification_date"));
            e.setObjectState(rs.getInt("object_state"));
            //e.setType(rs.getString("type"));

            HashMap<Long, Port> ports = new HashMap<Long, Port>();
            List<InnerConnection> innerConnections = new ArrayList<InnerConnection>();
            
            pst1 = conn.prepareStatement("Select * from port left join internal_connection "
                    + "on port.id=internal_connection.from_port_id where "
                    + "port.equipment_id="+e.getId()+";");
            
            rs1 = pst1.executeQuery();
            while(rs1.next()){
                Port port = new Port();
                port.setId(rs1.getLong("id"));
                port.setName(rs1.getString("name"));
                port.setCreationDate(rs1.getDate("creation_date"));
                port.setModificationDate(rs1.getDate("modification_date"));
                port.setObjectState(rs1.getInt("object_state"));
                ports.put( port.getId(), port);
                innerConnections.add(new InnerConnection(port.getId(), rs1.getLong("to_port_id")));                
            }
                
			for(int i = 0; i< innerConnections.size(); i++){
                 InnerConnection ic = innerConnections.get(i);
                 Port p1 = ports.get(ic.getFromPortID());
                 Port p2 = ports.get(ic.getToPortID());
                 p1.setInnerConnectedPort(p2);
             }
            
            e.setPorts((List<Port>)ports.values());
         
            
		} catch (SQLException exc)
		{
            Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, exc);
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (conn != null) conn.close();
				if (pst != null) pst.close();
				if (rs1 != null) rs1.close();
                if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
			}
		}	
		
        return e;

    }
    
    public List<Equipment> find(HashMap<String, String> searchCriterial){
        
        StringBuilder querySelectEquipment = new StringBuilder("Select * from Equipment where ");
        
        Set s = searchCriterial.entrySet();
        Iterator it = s.iterator();
        
        while(it.hasNext()){
            Map.Entry m =(Map.Entry)it.next();
            String key = (String)m.getKey();
            String value = (String)m.getValue();
            /*if (key.equals("id") || key.equals("object_state")) {
                querySelectEquipment.append(key+"="+value);
            }
            else if (key.equals("name") || key.equals("creation_date") ||
                     key.equals("modification_date") || key.equals("type") ||key.equals("address"))
            {
                querySelectEquipment.append(key+"="+value);
            }*/
            
            querySelectEquipment.append(key + "='"+value+"'");
            if (it.hasNext())
            querySelectEquipment.append(" AND ");
        }
        
        return getEquipmentsList(querySelectEquipment.toString());
    }
    
    private List<Equipment> getEquipmentsList(String query){
        Connection conn = null;
		PreparedStatement pst = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
		ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
		List<Equipment> equipments = null;
		try {
			conn = JDBCServise.getConnection();
        
			pst = conn.prepareStatement(query);
			rs = pst.executeQuery();
        
			equipments = new ArrayList<Equipment>();
        
			while (rs.next()) {
				Equipment e = new Equipment();
				e.setId(rs.getLong("id"));
				e.setName(rs.getString("name"));
				e.setAddress(rs.getString("address"));
				e.setCreationDate(rs.getDate("creation_date"));
                e.setModificationDate(rs.getDate("modification_date"));
                e.setObjectState(rs.getInt("object_state"));
                //e.setType(rs.getString("type"));
				
                HashMap<Long, Port> ports = new HashMap<Long, Port>();
                List<InnerConnection> innerConnections = new ArrayList<InnerConnection>();

                pst1 = conn.prepareStatement("Select * from port left join internal_connection "
                        + "on port.id=internal_connection.from_port_id where "
                        + "port.equipment_id="+e.getId()+";");

                rs1 = pst1.executeQuery();
                while(rs1.next()){
                    Port port = new Port();
                    port.setId(rs1.getLong("id"));
                    port.setName(rs1.getString("name"));
                    port.setCreationDate(rs1.getDate("creation_date"));
                    port.setModificationDate(rs1.getDate("modification_date"));
                    port.setObjectState(rs1.getInt("object_state"));
                    ports.put( port.getId(), port);
                    innerConnections.add(new InnerConnection(port.getId(), rs1.getLong("to_port_id")));                
                }

                for(int i = 0; i< innerConnections.size(); i++){
                     InnerConnection ic = innerConnections.get(i);
                     Port p1 = ports.get(ic.getFromPortID());
                     Port p2 = ports.get(ic.getToPortID());
                     p1.setInnerConnectedPort(p2);
                }
                e.setPorts((List<Port>) ports.values());
                equipments.add(e);
			}            
		} catch (SQLException exc)
		{
            Logger.getLogger(EquipmentProviderJDBC.class.getName()).log(Level.SEVERE, null, exc);
			throw new RuntimeException(exc);
		}
		finally
		{
			try {
				if (conn != null) conn.close();
				if (pst != null) pst.close();
				if (rs != null) rs.close();
			} catch (SQLException exc) {
				throw new RuntimeException(exc);
			}
		}	
		
        return equipments;
    }

    @Override
    public List<Equipment> getEquipmentsByType(String type) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Equipment> getEquipmentsByState(int objectState) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    
    private class InnerConnection{
        
        private long fromPortID;
        private long toPortID;
        
        public InnerConnection(long fromPortID, long toPortID ){
            this.fromPortID = fromPortID;
            this.toPortID = toPortID;
        }
        
        public long getFromPortID(){
            return fromPortID;
        }
        
        public long getToPortID(){
            return toPortID;
        }
        
    }
    
}
